import pymysql
import json
from myapp.utils.connection import POOL

# conn = pymysql.Connect(host='localhost', user='root', passwd='123456'
#                        , db='zhifou', port=3306, charset='utf8')
kw = '企业复工'  # 测试用
pidList = []


def check_generation(key, mid):
    # sql = "SELECT mid FROM {}_findson WHERE pid = 0".format(key)
    # cur = conn.cursor()
    # 4.执行sql命令
    # execute可执行数据库查询select和命令insert，delete，update三种命令(这三种命令需要commit()或rollback())
    # cur.execute(sql)
    # 5.获取数据
    # fetchall遍历execute执行的结果集。取execute执行后放在缓冲区的数据，遍历结果，返回数据。
    # 返回的数据类型是元组类型，每个条数据元素为元组类型:(('第一条数据的字段1的值','第一条数据的字段2的值',...,'第一条数据的字段N的值'),(第二条数据),...,(第N条数据))
    # midData = cur.fetchall()
    # for row in midData:
    #     pidList.append(row[0])
    #     check(key,row[0], 1)
    # print(pidList)
    # cur.close()
    check(key, mid, 1)


# 递归，刷新转发树
def check(key, mid, generation):
    conn = POOL.connection()
    counter = 0
    sql1 = "SELECT mid FROM {}_findson WHERE pid = {}".format(key, '+'+mid)
    cur = conn.cursor()
    cur.execute(sql1)
    resultSet = cur.fetchall()
    for result in resultSet:
        sql2 = "UPDATE {}_findson SET generation = {} WHERE mid = {}".format(
            key, generation, result[0])
        cur.execute(sql2)
        conn.commit()
        check(key, result[0], generation + 1)
        counter = counter + 1
    sql3 = "UPDATE {}_findson SET sons = {} WHERE mid = {}".format(
        key, counter, mid)
    cur.execute(sql3)
    conn.commit()
    cur.close()


# 找引爆点
def get_explosion_point(key, mid):
    conn = POOL.connection()
    sql = "SELECT userid,generation,sons FROM {}_findson " \
          "WHERE EXISTS(SELECT * FROM {}_userspider WHERE uid = userid) AND rootknot = {} AND " \
          "sons > 1 ORDER BY sons DESC LIMIT 9".format(key, key, mid)
    cur = conn.cursor()
    cur.execute(sql)
    tempResult = cur.fetchall()
    cur.close()
    print(tempResult)
    return tempResult


def explosion_loadjson(key, mid):
    # check_generation(key, mid)
    conn = POOL.connection()
    explosion_point = get_explosion_point(key, mid)
    userName = []
    cur = conn.cursor()
    for point in explosion_point:
        sql = "SELECT name FROM {}_userspider WHERE uid = {}".format(
            key, point[0])
        print(sql)
        cur.execute(sql)
        data = cur.fetchall()
        print(data)
        userName.append(data[0][0])

    json_file = []
    counter = -1
    for item in explosion_point:
        counter += 1
        if counter == 0:
            continue
        set = {}
        set['num'] = counter
        set['userName'] = userName[counter]
        set['userId'] = item[0]
        set['generation'] = item[1]
        set['sons'] = item[2]
        json_file.append(set)

    cur.close()

    conn.close()

    return {'data': json_file}


def writefile(key, mid):
    # check_generation(key)
    jsonData = explosion_loadjson(key, mid)
    jsondatar = json.dumps(jsonData, ensure_ascii=False)
    print(jsondatar)
    f = open(r'ExplosionPoint.json', 'w+', encoding="utf8")
    # 写数据
    f.write(jsondatar)
    # 关闭文件
    f.close()


if __name__ == '__main__':
    conn = POOL.connection()
    cur = conn.cursor()
    pre_sql = "SELECT mid FROM {}_findson WHERE pid = 0 ORDER BY reposts_count DESC LIMIT 1".format(
        kw)
    cur.execute(pre_sql)
    target_blog = cur.fetchall()[0][0]
    writefile(kw, target_blog)
